package csc.nndp.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.jws.soap.SOAPBinding.Use;

import csc.nndp.dao.User;


public class UserDB {

	private final static String username = "admin";
	private final static String password = "admin123";
	private final static String dbName = "jspservlet";
	private final static String tableName = "userdb";
	private final static String url = "jdbc:derby:" + dbName;
	private final static String protocol = "jdbc:derby:";
	private final static String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	static List<User> users =  new ArrayList<User>();
	public static List<User> getUserList() throws Exception {
		//List<User> users = new ArrayList<User>();
		try {

		     
			Connection connection = getConnection();
			Statement statement = connection.createStatement();
			String query = String.format("SELECT * FROM %s", tableName);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				int id = resultSet.getInt("userId");
				String userName = resultSet.getString("userName");
				String passWord = resultSet.getString("password");
				String firstName = resultSet.getString("firstName");
				String lastName = resultSet.getString("lastName");
				String phone = resultSet.getString("phone");
				String email = resultSet.getString("email");
				String role = resultSet.getString("role");

				users.add(new User(id, userName, passWord, firstName, lastName,
						phone, email, role));

			}
			connection.close();
		} catch (SQLException sqle) {
			throw sqle;
		}
		return users;
	}

	public static User getUserById(int userId) throws Exception {
		for (User e : getUserList()) {
			if (e.getUserId() == userId) {
				return e;
			}
		}
		return null;
	}
	
	public static User getUser(String userName, String password) throws Exception{
		for(User e: getUserList()){
			if(e.getUserName().equals(userName) && e.getPassword().equals(password)){
				return e;
			}
		}
		return null;
	}
	

	public static void addUser(User e) throws Exception {
		if (e != null) {
			String insertSql = String.format(
					"INSERT INTO %s VALUES(?, ?, ?, ?, ?,?,?,?)", tableName);
			Connection connection = getConnection();
			PreparedStatement inserter = connection.prepareStatement(insertSql);

			inserter.setInt(1, e.getUserId());
			inserter.setString(2, e.getUserName());
			inserter.setString(3, e.getPassword());
			inserter.setString(4, e.getFirstName());
			inserter.setString(5, e.getLastName());
			inserter.setString(6, e.getPhone());
			inserter.setString(7, e.getEmail());
			inserter.setString(8, e.getRole());

			inserter.execute();

		
			inserter.close();
			connection.close();
		}
	}

	public static void updateUser(User e) throws Exception {
		if (e != null) {
			String updateSql = String.format(
					"UPDATE %s SET userName=? ,password=? , firstname =?, "
							+ "lastname=?, phone=?, email=?,role=? WHERE userId=?",
					tableName);
			Connection connection = getConnection();
			PreparedStatement updator = connection.prepareStatement(updateSql);

			updator.setString(1, e.getUserName());
			updator.setString(2, e.getPassword());
			updator.setString(3, e.getFirstName());
			updator.setString(4, e.getLastName());
			updator.setString(5, e.getPhone());
			updator.setString(6, e.getEmail());
			updator.setString(7, e.getRole());

			updator.setInt(8, e.getUserId());
			updator.execute();

			// Close stm and con
			updator.close();
			connection.close();
		}
	}
	
	
	public static void delete(User e) throws Exception {
		if (e != null) {
			String deleteSql = String.format(
					"Delete from %s WHERE userId=%s",
					tableName,e.getUserId());
			Connection connection = getConnection();
			PreparedStatement deleter = connection.prepareStatement(deleteSql);

	
		
			//deleter.setInt(1, e.getUserId());
			deleter.execute();

			// Close stm and con
			deleter.close();
			connection.close();
		}
	}
	
	
	public static Connection getConnection() 
		      throws Exception {
		Class.forName(driver).newInstance();
		Properties userInfo;
		userInfo = new Properties();
	    userInfo.put("user", username);
	    userInfo.put("password", password);
		 String dbUrl = protocol + dbName;
		Connection connection = DriverManager.getConnection(dbUrl, userInfo);
		return(connection);
/*		    
			Class.forName(driver);
		    
			Properties userInfo = new Properties();
		    userInfo.put("user", username);
		    userInfo.put("password", password);
		    Connection connection =
		      DriverManager.getConnection("jdbc:derby:jspservlet", userInfo);*/
		    
		  }


}
